package ssm.Test;

import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.annotation.Rollback;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.transaction.annotation.Transactional;
import ssm.Dao.SurplusDao;
import ssm.Dao.UserDao;
import ssm.Pojo.YzUsers;
import ssm.Tools.EncryptUtil;

import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @author Maple.Chen
 * @date 2020年3月20日
 * @description <p>单元测试</p>
 */
@ContextConfiguration(locations = { "classpath:applicationContext.xml" })
@RunWith(value = SpringJUnit4ClassRunner.class)
@Transactional
public class Excel_test {

	// import Excel方法用到的常量
	private final static String excel2003L = ".xls"; // 2003- 版本的excel
	private final static String excel2007U = ".xlsx"; // 2007+ 版本的excel

	@Autowired
	private UserDao ud;

	@Autowired
	private SurplusDao spd;

	@Autowired
	private SqlSessionFactory sqlSessionFactory;

	@Test
	public void MD5(){
		System.out.println("一次" + EncryptUtil.getMD5String("123456"));
		System.out.println("二次" + EncryptUtil.getMD5String(EncryptUtil.getMD5String("123456")));
	}

	@Test
	@Rollback(false)//false:关闭单元测试的保护机制，对数据库进行实际增删改操作
	public void batchInsert_Excel_Import() throws NoSuchMethodException, IllegalAccessException, InvocationTargetException, InterruptedException {
        long startTime = System.currentTimeMillis();
		List<YzUsers> list=Import(new File("G:"+File.separator+"test"+File.separator+"yz_user_Excel_test.xls"));
		/**
		 * 每个线程插入的数据为1000 <--- 最优效率
		 * 根据数据记录数获得线程总数，但是并发只有10个线程
		 * 根据线程总数对数据进行切割
		 */
		int mod_=1000;
		int Thread_size=(list.size()%mod_==0)?(list.size()/mod_):((list.size()/mod_)+1);
		List<List<YzUsers>> lis = new ArrayList<>(Thread_size);
		for(int i=0;i<Thread_size;i++){ //[a,b) [0,1000)
			if(i==(Thread_size-1)){
				if(list.size()%mod_>0){
					lis.add(list.subList(i*mod_, i*mod_+list.size()%1000));
				}else{
					lis.add(list.subList(i*mod_, i*mod_+mod_));
				}
			}else{
				lis.add(list.subList(i*mod_, i*mod_+mod_));
			}
		}
		long endTime = System.currentTimeMillis();
		System.out.println("解析Excel所耗费的时间：" + (endTime - startTime) + "ms");
		System.out.println(lis.size()+"/"+Thread_size);
//		Reflect(lis.get(lis.size()-1).get(lis.get(lis.size()-1).size()-1));  //用反射方法代替toString()  遍历某个实体对象所有属性
//		ExecutorService executor = Executors.newFixedThreadPool(Thread_size);
		Thread[] ts=new Thread[Thread_size];
		long startTime2 = System.currentTimeMillis();
		for(int i=0;i<Thread_size;i++){
			ts[i]=new Thread(new Thread1(lis.get(i),ud,spd,sqlSessionFactory));
			ts[i].start();
			ts[i].join();
//			Thread1 th=new Thread1(lis.get(i),ud,spd,sqlSessionFactory);
//			executor.execute(th);
		}
//		while(!executor.isTerminated()){
//			if(executor.isTerminated())
//				break;
//		}
//		executor.shutdown();
		long endTime2 = System.currentTimeMillis();
		System.out.println("执行Thread所耗费的时间：" + (endTime2 - startTime2) + "ms");
	}

	@Test
	public void Excel_Export(){
		long startTime1 = System.currentTimeMillis();
		List<YzUsers> list=new ArrayList<>();
		YzUsers yu;
		for(int i=0;i<70000;i++){
			yu=new YzUsers();
			yu.setId("u"+Integer.toString(i+1));
			yu.setRealname("张"+Integer.toString(i));
			yu.setUsername("zhangsan"+Integer.toString(i));
			// 使用MD5一次加密后的串存入数据库
			yu.setPassword(EncryptUtil.getMD5String("123456"));
			// 允许为空的部分
			yu.setQq("qq_"+Integer.toString(i+1));
			yu.setEmail("email_"+Integer.toString(i+1));
			yu.setWechat("WeChat_"+Integer.toString(i+1));
			yu.setMobile("mobile_"+Integer.toString(i+1));
			yu.setAddress("address_"+Integer.toString(i+1));
			// 年龄 随机数[16,35)
			yu.setAge((int) Math.random()*(35-16)+16);
			yu.setSex("男");
			yu.setDerive("Earth");
			yu.setHobby("this.爱好");
			yu.setU_img("https://www.baidu.com/");
			list.add(yu);
		}
		String Storage_path = "G:"+File.separator+"test"+File.separator;
		boolean result=Export(list,Storage_path,"yz_user_Excel_test");
		if(result){
			long endTime1 = System.currentTimeMillis();
			System.out.println("生成Excel所耗费的时间：" + (endTime1 - startTime1) + "ms");
		}
	}

	// 将Test方法生成的测试数据输出至Excel文件
	public static boolean Export(List<YzUsers> u_list,String Storage_path,String FileName){
		boolean exec_result = false;
		// 创建一个Excel文件
		HSSFWorkbook workbook = new HSSFWorkbook();
		/**
		 * 下面先查看总数据数，每张sheet仅能存储65535行数据
		 */
		int sheet_size=(u_list.size()%65535==0)?(u_list.size()/65535):((u_list.size()/65535)+1);
		List<List<YzUsers>> lis=new ArrayList<>(sheet_size);
		int index=0;
		for(int i=0;i<sheet_size;i++){
			if(i==(sheet_size-1)){
				lis.add(u_list.subList(index, index+u_list.size()%65535));
			}else{
				lis.add(u_list.subList(index, index+65535));
			}
			index+=65535;
		}
		for(int page=0;page<sheet_size;page++){
			// 创建一个工作表
			HSSFSheet sheet = workbook.createSheet("sheet"+Integer.toString(page+1));
			// 添加表头行
			HSSFRow hssfRow = sheet.createRow(0);
			// 设置单元格格式居中
			HSSFCellStyle cellStyle = workbook.createCellStyle();
			cellStyle.setAlignment(HorizontalAlignment.CENTER);

			// 添加表头内容
			HSSFCell headCell = hssfRow.createCell(0);
			headCell.setCellValue("id");
			headCell.setCellStyle(cellStyle);

			headCell = hssfRow.createCell(1);
			headCell.setCellValue("真实姓名");
			headCell.setCellStyle(cellStyle);

			headCell = hssfRow.createCell(2);
			headCell.setCellValue("账号");
			headCell.setCellStyle(cellStyle);

			headCell = hssfRow.createCell(3);
			headCell.setCellValue("密码");
			headCell.setCellStyle(cellStyle);
			// 允许为空部分
			headCell = hssfRow.createCell(4);
			headCell.setCellValue("QQ");
			headCell.setCellStyle(cellStyle);

			headCell = hssfRow.createCell(5);
			headCell.setCellValue("Email");
			headCell.setCellStyle(cellStyle);

			headCell = hssfRow.createCell(6);
			headCell.setCellValue("WeChat");
			headCell.setCellStyle(cellStyle);

			headCell = hssfRow.createCell(7);
			headCell.setCellValue("Mobile");
			headCell.setCellStyle(cellStyle);

			headCell = hssfRow.createCell(8);
			headCell.setCellValue("Address");
			headCell.setCellStyle(cellStyle);

			headCell = hssfRow.createCell(9);
			headCell.setCellValue("Age");
			headCell.setCellStyle(cellStyle);

			headCell = hssfRow.createCell(10);
			headCell.setCellValue("Sex");
			headCell.setCellStyle(cellStyle);

			headCell = hssfRow.createCell(11);
			headCell.setCellValue("籍贯");
			headCell.setCellStyle(cellStyle);

			headCell = hssfRow.createCell(12);
			headCell.setCellValue("爱好");
			headCell.setCellStyle(cellStyle);

			headCell = hssfRow.createCell(13);
			headCell.setCellValue("头像图");
			headCell.setCellStyle(cellStyle);

			// 添加数据内容
			for (int i = 0; i < lis.get(page).size(); i++) {
				hssfRow = sheet.createRow((int) i + 1);
				YzUsers y = (YzUsers) lis.get(page).get(i);

				// 创建单元格，并设置值
				HSSFCell cell = hssfRow.createCell(0);
				cell.setCellValue(y.getId());
				cell.setCellStyle(cellStyle);

				cell = hssfRow.createCell(1);
				cell.setCellValue(y.getRealname());
				cell.setCellStyle(cellStyle);

				cell = hssfRow.createCell(2);
				cell.setCellValue(y.getUsername());
				cell.setCellStyle(cellStyle);

				cell = hssfRow.createCell(3);
				cell.setCellValue(y.getPassword());
				cell.setCellStyle(cellStyle);
				// Others
				cell = hssfRow.createCell(4);
				cell.setCellValue(y.getQq());
				cell.setCellStyle(cellStyle);

				cell = hssfRow.createCell(5);
				cell.setCellValue(y.getEmail());
				cell.setCellStyle(cellStyle);

				cell = hssfRow.createCell(6);
				cell.setCellValue(y.getWechat());
				cell.setCellStyle(cellStyle);

				cell = hssfRow.createCell(7);
				cell.setCellValue(y.getMobile());
				cell.setCellStyle(cellStyle);

				cell = hssfRow.createCell(8);
				cell.setCellValue(y.getAddress());
				cell.setCellStyle(cellStyle);

				cell = hssfRow.createCell(9);
				cell.setCellValue(y.getAge());
				cell.setCellStyle(cellStyle);

				cell = hssfRow.createCell(10);
				cell.setCellValue(y.getSex());
				cell.setCellStyle(cellStyle);

				cell = hssfRow.createCell(11);
				cell.setCellValue(y.getDerive());
				cell.setCellStyle(cellStyle);

				cell = hssfRow.createCell(12);
				cell.setCellValue(y.getHobby());
				cell.setCellStyle(cellStyle);

				cell = hssfRow.createCell(13);
				cell.setCellValue(y.getU_img());
				cell.setCellStyle(cellStyle);

			}
		}
		// 保存Excel文件
		try {
			String Path=Storage_path+FileName+".xls";
			OutputStream outputStream = new FileOutputStream(Path);
			workbook.write(outputStream);
			outputStream.close();
			exec_result = true;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return exec_result;
	}

	// 解析指定格式Excel文件
	public static List<YzUsers> Import(File file){
		List<YzUsers> list = new ArrayList<>();
		YzUsers yu;
		FileInputStream fis = null;
		try {
			fis = new FileInputStream(file);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		}
		Workbook work = null;
		String fileType = file.getName().substring(file.getName().lastIndexOf("."));
		try{
			if (excel2003L.equals(fileType)) {
				work = new HSSFWorkbook(fis); // 2003-
			} else if (excel2007U.equals(fileType)) {
				work = new XSSFWorkbook(fis); // 2007+
			} else {
				// "解析的文件格式有误！"
				return null;
			}
			Sheet sheet = null;
			// 因为Excel不一定只有一张sheet，遍历Excel中所有的sheet
			for (int i = 0; i < work.getNumberOfSheets(); i++) {
				sheet = work.getSheetAt(i);
				if (sheet == null)
					continue;
				//遍历当前sheet的每一行
				Row row ;
				for (int j = 1; j < sheet.getLastRowNum() + 1; j++) {
					row = sheet.getRow(j);
					// 遍历所有的列
					yu = new YzUsers();
					for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
						//System.out.print(getCellValue(row.getCell(y))+" ");
						if(y==0) yu.setId((String) getCellValue(row.getCell(y)));
						if(y==1) yu.setRealname((String) getCellValue(row.getCell(y)));
						if(y==2) yu.setUsername((String) getCellValue(row.getCell(y)));
						if(y==3) yu.setPassword((String) getCellValue(row.getCell(y)));
						// Others
						if(y==4) yu.setQq((String) getCellValue(row.getCell(y)));
						if(y==5) yu.setEmail((String) getCellValue(row.getCell(y)));
						if(y==6) yu.setWechat((String) getCellValue(row.getCell(y)));
						if(y==7) yu.setMobile((String) getCellValue(row.getCell(y)));
						if(y==8) yu.setAddress((String) getCellValue(row.getCell(y)));

						if(y==9) yu.setAge(Integer.parseInt((String) getCellValue(row.getCell(y))));
						if(y==10) yu.setSex((String) getCellValue(row.getCell(y)));
						if(y==11) yu.setDerive((String) getCellValue(row.getCell(y)));
						if(y==12) yu.setHobby((String) getCellValue(row.getCell(y)));
						if(y==13) yu.setU_img((String) getCellValue(row.getCell(y)));
					}
					list.add(yu);
				}
			}
			work.close();
		}catch(Exception e){
			e.printStackTrace();
		}
		return list;
	}

	/**
	 * 描述：对表格中数值进行格式化
	 *
	 * @param cell
	 * @return
	 */
	public static Object getCellValue(Cell cell) {
		Object value = null;
		DecimalFormat df = new DecimalFormat("0"); // 格式化number String字符
		SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd"); // 日期格式化
		DecimalFormat df2 = new DecimalFormat("0"); // 格式化数字

		switch (cell.getCellTypeEnum()) {
			case STRING:
				value = cell.getRichStringCellValue().getString();
				break;
			case NUMERIC:
				if ("General".equals(cell.getCellStyle().getDataFormatString())) {
					value = df.format(cell.getNumericCellValue());
				} else if ("m/d/yy".equals(cell.getCellStyle().getDataFormatString())) {
					value = sdf.format(cell.getDateCellValue());
				} else {
					value = df2.format(cell.getNumericCellValue());
				}
				break;
			case BOOLEAN:
				value = cell.getBooleanCellValue();
				break;
			case BLANK:
				value = "";
				break;
			default:
				break;
		}
		return value;
	}

	/**
	 * 方法反射：遍历属性
	 */
	 public void Reflect(Object model) throws NoSuchMethodException, InvocationTargetException, IllegalAccessException {
		 Field[] field =model.getClass().getDeclaredFields();
		 for(int j=0 ; j<field.length ; j++) {     //遍历所有属性
			 String name = field[j].getName();    //获取属性的名字
			 name = name.substring(0,1).toUpperCase()+name.substring(1); //将属性的首字符大写，方便构造get，set方法
			 String type = field[j].getGenericType().toString();    //获取属性的类型
			 if(type.equals("class java.lang.String")){   //如果type是类类型，则前面包含"class "，后面跟类名
				 Method m = model.getClass().getMethod("get"+name);
				 String value = (String) m.invoke(model);    //调用getter方法获取属性值
				 if(value != null){
					 System.out.println(name+".value="+value);
				 }
			 }
			 if(type.equals("class java.lang.Integer")){
				 Method m = model.getClass().getMethod("get"+name);
				 Integer value = (Integer) m.invoke(model);
				 if(value != null){
					 System.out.println(name+".value="+value);
				 }
			 }
			 if(type.equals("class java.lang.Short")){
				 Method m = model.getClass().getMethod("get"+name);
				 Short value = (Short) m.invoke(model);
				 if(value != null){
					 System.out.println(name+".value="+value);
				 }
			 }
			 if(type.equals("class java.lang.Double")){
				 Method m = model.getClass().getMethod("get"+name);
				 Double value = (Double) m.invoke(model);
				 if(value != null){
					 System.out.println(name+".value="+value);
				 }
			 }
			 if(type.equals("class java.lang.Boolean")){
				 Method m = model.getClass().getMethod("get"+name);
				 Boolean value = (Boolean) m.invoke(model);
				 if(value != null){
					 System.out.println(name+".value="+value);
				 }
			 }
			 if(type.equals("class java.util.Date")){
				 Method m = model.getClass().getMethod("get"+name);
				 Date value = (Date) m.invoke(model);
				 if(value != null){
					 System.out.println(name+".value="+value);
				 }
			 }
		 }
	 }
}
